using System;
using System.Collections.Generic;
using System.Text;
using QLKS.HeThongBUS;
using System.Data;
using System.Data.OleDb;
using QLKS.HeThongDAO;


namespace QLKS.HeThongDAO
{
    public class PH_DAO
    {
        public static DataTable SelectAllLoaiPH()
        {
            string sql = "SELECT MaLoaiPhong as [Mã Loại Phòng], TenLoaiPhong as [Tên Loại Phòng], GiaPhong as [Giá Phòng],GhiChu as [Ghi Chú] FROM LoaiPhong";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        public static bool Insert(PH_DTO lph)
        {
            try
            {

                string sql = "INSERT INTO LoaiPhong(TenLoaiPhong,GiaPhong, GhiChu) VALUES ('" +
                lph.TenLP + "','" + lph.GiaLP + "','" + lph.GhichuLP + "')";
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool UpdateLP(PH_DTO LP)
        {
            try
            {
                string sql = "UPDATE LoaiPhong SET MaLoaiPhong = '" + LP.GiaLP + "',TenLoaiPhong = '" + LP.TenLP + "',GiaPhong = "+ LP.GiaLP+",GhiChu = '" + LP.GhichuLP+"'";
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool DeleteLP(PH_DTO LP)
        {
            try
            {
                string sql = "DELETE FROM LoaiPhong WHERE MaLoaiPhong=" + LP.MaLP;
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }



        //danh sach phong
        public static DataTable SelectAllDSPhong()
        {
            string sql = "SELECT MaPhong as [Mã Phòng],TenLoaiPhong as [Loại Phòng],TinhTrang as [Tình Trạng],Tang as [Tầng],LoaiPhong FROM LoaiPhong,Phong where Phong.LoaiPhong=LoaiPhong.MaLoaiPhong";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        //danh sach phong còn trống
        public static DataTable SelectAllDSPhongTrong()
        {
            string sql = "SELECT MaPhong as [Mã Phòng],TenLoaiPhong as [Loại Phòng],TinhTrang as [Tình Trạng],Tang as [Tầng],LoaiPhong FROM LoaiPhong,Phong where Phong.LoaiPhong=LoaiPhong.MaLoaiPhong And TinhTrang="+0+"";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        //tra cuu
        public static DataTable Tracuuphong(string dk1,string dk2,string dk3)
        {
            string sql = "SELECT MaPhong as [Mã Phòng],TenLoaiPhong as [Loại Phòng],TinhTrang as [Tình Trạng],Tang as [Tầng],LoaiPhong FROM LoaiPhong,Phong where Phong.LoaiPhong=LoaiPhong.MaLoaiPhong "+dk1+" "+dk2+" "+dk3+"";
            return Config.AccessHelper.ExecuteQuery(sql);
        }



  #region Quan ly Phong
        
        //them
        public static bool InsertPH(PH_DTO ph)
        {
            try
            {
                //tao cau truy van va thuc thi cau truy van
                string sql = "INSERT INTO Phong(MaPhong, LoaiPhong, TinhTrang,Tang) VALUES ('" +
                ph.Maphong + "','" +
                ph.MaLP + "','" +
                0 +"','" +
                ph.Tang + "')";
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }


        //cap nhat
        public static bool UpdatePH(PH_DTO ph)
        {
            try
            {
                string sql = "UPDATE Phong SET LoaiPhong = '" + ph.MaLP + "',Tang = '" + ph.Tang + "' where MaPhong LIKE " + ph.Maphong;
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }


        //xoa

        public static bool DeletePH(PH_DTO ph)
        {
            try
            {
                string sql = "DELETE FROM Phong WHERE MaPhong LIKE " + ph.Maphong;
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

   #endregion


    }
}
